package com.x.erp.dao;

import com.x.erp.model.Manufacturer;
import com.x.erp.model.Relation;
import com.x.framework.dao.BaseDao;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class ManufacturerDao extends BaseDao {
   private final static String selectManufacturerSql
           = "SELECT M.*,A.zs FROM x_base.BASE_MANUFACTURER M JOIN x_base.BASE_BRAND_MANUFACTURER BM ON M.MANUFACTURER_ID = BM.MANUFACTURER_ID" +
           " left join (select B.MANUFACTURER_ID, count(1) as zs from x_base.third_manufacturer_check B where B.MANUFACTURER_PASS = 0) as A on A.MANUFACTURER_ID = M.MANUFACTURER_ID " +
           "  WHERE 1 = 1 ";

   public List<Manufacturer> selectManufacturer(Manufacturer manufacturer) throws Exception {
       StringBuilder sb = new StringBuilder(selectManufacturerSql);
       List<Object> params = new ArrayList<Object>();
       if(manufacturer.getManufacturerId() != null){
           sb.append("AND M.MANUFACTURER_ID = ? ");
           params.add(manufacturer.getManufacturerId());
       }
       if(this.isString(manufacturer.getManufacturerName())){
           sb.append("AND M.MANUFACTURER_NAME = ? ");
           params.add(manufacturer.getManufacturerName());
       }
       if(manufacturer.getCountryId() != null){
           sb.append("AND M.COUNTRY_ID = ? ");
           params.add(manufacturer.getCountryId());
       }
       if(manufacturer.getManufacturerState() == null){
           sb.append("AND M.MANUFACTURER_STATE = 1 ");
       }else{
           sb.append("AND M.MANUFACTURER_STATE = ? ");
           params.add(manufacturer.getManufacturerState());
       }
       if(manufacturer.getBrandId() != null){
           sb.append("AND BM.BRAND_ID = ? ");
           params.add(manufacturer.getBrandId());
       }
       if(manufacturer.getTruePage() != null ){
           this.getLimitSql(sb, manufacturer) ;
       }
       return this.queryForList(sb.toString(), Manufacturer.class, params);
   }

    //添加
    private final static String insertManufacturerListSql = "INSERT INTO x_base.BASE_MANUFACTURER(MANUFACTURER_ID,MANUFACTURER_NAME,MANUFACTURER_STATE) VALUES ";
    public void insertManufacturerList(Manufacturer manufacturer){
        StringBuilder sql = new StringBuilder(insertManufacturerListSql);
        List<Object> params = new ArrayList<>();
        if(manufacturer != null){
            sql.append("(?,?,?)") ;
            params.add(manufacturer.getManufacturerId());
            params.add(manufacturer.getManufacturerName());
            params.add(manufacturer.getManufacturerState());
            this.execute(sql.toString(), params);
        }
    }

    //修改
    public void updateManufacturerList(Manufacturer manufacturer) throws Exception{
        if(manufacturer != null){
            StringBuilder sql = new StringBuilder();
            List<Object> params = new ArrayList<>();
            sql.append("UPDATE x_base.BASE_MANUFACTURER set MANUFACTURER_NAME=?,MANUFACTURER_STATE=? where MANUFACTURER_ID = ? ") ;
            params.add(manufacturer.getManufacturerName());
            params.add(manufacturer.getManufacturerState());
            params.add(manufacturer.getManufacturerId());
            this.execute(sql.toString(), params);
        }
    }

    //删除中间表
    public void delBrandManufacturer(Integer brandId, Integer manufacturerId) throws Exception{
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sql.append("DELETE from x_base.BASE_BRAND_MANUFACTURER where MANUFACTURER_ID = ? and BRAND_ID = ? ") ;
        params.add(manufacturerId);
        params.add(brandId);
        this.execute(sql.toString(), params);
    }

    // 插入中间表
    public void insertBrandManufacturer(Integer brandId, Integer manufacturerId) throws Exception{
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sql.append("INSERT INTO x_base.BASE_BRAND_MANUFACTURER(BRAND_ID,MANUFACTURER_ID) VALUES ") ;
        if(brandId != null && manufacturerId != null){
            sql.append("(?,?)") ;
            params.add(brandId);
            params.add(manufacturerId);
            this.execute(sql.toString(), params);
        }
    }

    //查询中间表
    public int findBrandManufacturer(Integer brandId, Integer manufacturerId) throws Exception{
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sql.append("select count(1) from x_base.BASE_BRAND_MANUFACTURER where MANUFACTURER_ID = ? and BRAND_ID = ? ") ;
        params.add(manufacturerId);
        params.add(brandId);
        return this.queryForInt(sql.toString(), params);
    }

    /**
     * 按照manufacturer id 查询
     */
    public Manufacturer getSimpleManufacturer(Map<String, Object> manufacturer) throws Exception{
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT MANUFACTURER_NAME,MANUFACTURER_STATE,MANUFACTURER_ID "
                +" FROM x_base.BASE_MANUFACTURER where true ") ;
        List<Object> params = new ArrayList<>();
        if(manufacturer.containsKey("manufacturerId")){
            params.add(manufacturer.get("manufacturerId"));
            sql.append(" and MANUFACTURER_ID = ? ") ;
        }
        if(manufacturer.containsKey("brandId")){
            params.add(manufacturer.get("brandId"));
            sql.append(" and BRAND_ID = ? ") ;
        }
        return (Manufacturer) this.queryForObject(sql.toString(), Manufacturer.class, params);

    }

    /**
     * 查询中间表列表
     */
    public List<Relation> getRelationList(Map<String,Object> relation) throws Exception {
        StringBuffer sql = new StringBuffer() ;
        sql.append("SELECT b.*,c.* ,n.zs from x_base.base_brand_manufacturer as a join x_base.base_brand as b on a.BRAND_ID = b.BRAND_ID join x_base.base_manufacturer as c on a.MANUFACTURER_ID = c.MANUFACTURER_ID "
                +" left join (select d.BRAND_ID,d.MANUFACTURER_ID, count(0) as zs from x_base.third_brand_manufacturer_check as d where d.BRAND_MANUFACTURER_PASS=0) as n on a.BRAND_ID = n.BRAND_ID and n.MANUFACTURER_ID = c.MANUFACTURER_ID  where true");
        List<Object> params = new ArrayList<>();
        if(relation.containsKey("manufacturerId")){
            params.add(relation.get("manufacturerId"));
            sql.append(" and a.MANUFACTURER_ID = ? ") ;
        }
        if(relation.containsKey("brandId")){
            params.add(relation.get("brandId"));
            sql.append(" and a.BRAND_ID = ? ") ;
        }
        if(relation.containsKey("truePage")) {
            sql.append(" LIMIT ").append(Integer.valueOf(relation.get("pageIndex").toString())-1).append(",").append(Integer.valueOf(relation.get("pageSize").toString()));
        } else {
            sql.append(" LIMIT 5");
        }
        return this.queryForList(sql.toString(), Relation.class,params);
    }

    /**
     * 分页Sql
     * @param sql
     * @param manufacturer
     */
    private void getLimitSql(StringBuilder sql, Manufacturer manufacturer) {
        if(manufacturer.getPageSize() != null && manufacturer.getPageSize() > 0) {
            sql.append(" LIMIT ").append(manufacturer.getPageIndex()-1).append(",").append(manufacturer.getPageSize());
        } else {
            sql.append(" LIMIT 5");
        }
    }
}
